package com.zhoufeng.db;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.Date;

import com.jfinal.plugin.activerecord.Db;
import com.jfinal.plugin.activerecord.Record;

public class DbMng {
	public synchronized static boolean saveinfo(String s) {
		boolean flag = false;
		Connection con = null;
		PreparedStatement pstmt = null, pstmt2 = null, pstmt3 = null, pstmtupdate = null, pstmtupdatesetonoroff = null,pstmtaddaftersetcount=null;
		String[] ss = s.split(" ");
		String sql = "INSERT INTO thing_device_recievedata (id,recievedate,frequency,formaldehyde,mode,modeswitch,pm25inhigh,pm25inlow,pm25outhigh,pm25outlow,co2high,co2low,enteringairtempin,"
				+ "leavingairtempin,enteringairtempout,leavingairtempout,humidity,message,directiveno,checksum,messagetrailer) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";

		String sqlupdatesetonoroff = "update thing_device_info set setonoroff = 3 where (select onoroff from thing_device where id= thing_device_info.id ) = 0 and id = ?";
		
		//更新最后一次设置后的数据报接收数量
		String sqladdaftersetcount = "update thing_device_info set aftersetcount = aftersetcount + 1 where id = ? ";
		
//		//根据发来的数据报更新服务器设置
//		String sqlupdatesetter = "update thing_device_info set  model = (case length(model) when 2 then concat('1',?) else ? end ) , "
//				+ "functionalswitch = (case length(functionalswitch) when 2 then concat('1',?) else ? end ) , frequency=? where id = ? and aftersetcount < 3";

		// 收到设备信息，设备自动转为在线状态 onoroff = 1
		String sqlupdate = "update thing_device set recievedate=?,frequency=?,formaldehyde=?,mode=?,modeswitch=?,pm25in=?,pm25out=?,co2=?,enteringairtempin=?,"
				+ "leavingairtempin=?,enteringairtempout=?,leavingairtempout=?,humidity=?, onoroff = 1 , primaryfilterlife_overdue=?,electrostaticdustoflife_overdue=?,highefficiencyfilterlife_overdue=? "
				+ "where id=?";
		
		//String sqlupdatesetting = "update thing_device_info set model = (case length(model) when 2 then concat('1',?) else ? end ) ,"
		//		+ "functionalswitch = (case length(functionalswitch) when 2 then concat('1',?) else ? end )  where id = ?";
		

		String sql2 = "INSERT INTO thing_device (id,recievedate,frequency,formaldehyde,mode,modeswitch,pm25in,pm25out,co2,enteringairtempin,"
				+ "leavingairtempin,enteringairtempout,leavingairtempout,humidity) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";

		String sql3 = "INSERT INTO thing_device_alldata (id,recievedate,frequency,formaldehyde,mode,modeswitch,pm25in,pm25out,co2,enteringairtempin,"
				+ "leavingairtempin,enteringairtempout,leavingairtempout,humidity) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
		try {
			con = ConnectionManager.getConnection(false); // DbKit.getConfig().getConnection();
			pstmt = con.prepareStatement(sql);
			// System.out.println(new Integer(Integer.parseInt(ss[2],
			// 16)).toString());
			// System.out.println(new Integer(Integer.parseInt(ss[7],
			// 16)).toString() + new Integer(Integer.parseInt(ss[6],
			// 16)).toString());
			// System.out.println(new Integer(Integer.parseInt(ss[9],
			// 16)).toString() + new Integer(Integer.parseInt(ss[8],
			// 16)).toString());
			// System.out.println(new Integer(Integer.parseInt(ss[11],
			// 16)).toString() + new Integer(Integer.parseInt(ss[10],
			// 16)).toString());
			// System.out.println(new Integer(Integer.parseInt(ss[12],
			// 16)).toString());
			int primaryfilterlife_overdue = 0, electrostaticdustoflife_overdue = 0, highefficiencyfilterlife_overdue = 0;

			pstmt.setString(1, ss[19] + ss[18] + ss[17]);
			pstmt.setLong(2, new Date().getTime());
			pstmt.setString(3, ss[2]);
			pstmt.setString(4, ss[3]);
			pstmt.setString(5, ss[4]);
			pstmt.setString(6, ss[5]);
			pstmt.setString(7, ss[6]);
			pstmt.setString(8, ss[7]);
			pstmt.setString(9, ss[8]);
			pstmt.setString(10, ss[9]);
			pstmt.setString(11, ss[10]);
			pstmt.setString(12, ss[11]);
			pstmt.setString(13, ss[12]);
			pstmt.setString(14, ss[13]);
			pstmt.setString(15, ss[14]);
			pstmt.setString(16, ss[15]);
			pstmt.setString(17, ss[16]);
			pstmt.setString(18, ss[0]);
			pstmt.setString(19, ss[1]);
			pstmt.setString(20, ss[20]);
			pstmt.setString(21, ss[21].substring(0, 2));

			if ("11".equals(ss[5]) || "12".equals(ss[5]) || "13".equals(ss[5])) {
				highefficiencyfilterlife_overdue = 1;
			} else if ("21".equals(ss[5]) || "22".equals(ss[5]) || "23".equals(ss[5])) {
				primaryfilterlife_overdue = 1;
			} else if ("31".equals(ss[5]) || "32".equals(ss[5]) || "33".equals(ss[5])) {
				electrostaticdustoflife_overdue = 1;
			}

			pstmt.execute();
			pstmt.close();

			pstmtupdatesetonoroff = con.prepareStatement(sqlupdatesetonoroff);
			pstmtupdatesetonoroff.setString(1, ss[19] + ss[18] + ss[17]);
			pstmtupdatesetonoroff.execute();
			pstmtupdatesetonoroff.close();
			
			
			pstmtaddaftersetcount = con.prepareStatement(sqladdaftersetcount);
			pstmtaddaftersetcount.setString(1, ss[19] + ss[18] + ss[17]);
			pstmtaddaftersetcount.execute();
			pstmtaddaftersetcount.close();
			
			
//			String model = ss[4].length() > 1 ? ss[4].substring(1):ss[4];
//			String functionswitch = ss[5].length() > 1 ? ss[5].substring(1):ss[5];
//			String frequency = new Integer(Integer.parseInt(ss[2], 16)).toString();
//			
//			pstmtupdatesetter = con.prepareStatement(sqlupdatesetter);
//			pstmtupdatesetter.setString(1, model);
//			pstmtupdatesetter.setString(2, model);
//			pstmtupdatesetter.setString(3, functionswitch);
//			pstmtupdatesetter.setString(4, functionswitch);
//			pstmtupdatesetter.setString(5, frequency);
//			pstmtupdatesetter.setString(6, ss[19] + ss[18] + ss[17]);
//			pstmtupdatesetter.execute();
//			pstmtupdatesetter.close();
			

			pstmtupdate = con.prepareStatement(sqlupdate);

			pstmtupdate.setLong(1, new Date().getTime());
			pstmtupdate.setString(2, new Integer(Integer.parseInt(ss[2], 16)).toString());
			pstmtupdate.setString(3, new Integer(Integer.parseInt(ss[3], 16)).toString());
			pstmtupdate.setString(4, new Integer(Integer.parseInt(ss[4], 16)).toString());
			pstmtupdate.setString(5, new Integer(Integer.parseInt(ss[5], 16)).toString());
			// pstmtupdate.setString(6, new Integer(Integer.parseInt(ss[7],
			// 16)).toString() + new Integer(Integer.parseInt(ss[6],
			// 16)).toString());
			pstmtupdate.setString(6, new Integer(Integer.parseInt(ss[7], 16) * 256 + Integer.parseInt(ss[6], 16)).toString());
			// pstmtupdate.setString(7, new Integer(Integer.parseInt(ss[9],
			// 16)).toString() + new Integer(Integer.parseInt(ss[8],
			// 16)).toString());
			pstmtupdate.setString(7, new Integer(Integer.parseInt(ss[9], 16) * 266 + Integer.parseInt(ss[8], 16)).toString());
			// pstmtupdate.setString(8, new Integer(Integer.parseInt(ss[11],
			// 16)).toString() + new Integer(Integer.parseInt(ss[10],
			// 16)).toString());
			pstmtupdate.setString(8, new Integer(Integer.parseInt(ss[11], 16) * 256 + Integer.parseInt(ss[10], 16)).toString());
			pstmtupdate.setString(9, new Integer(Integer.parseInt(ss[12], 16)).toString());
			pstmtupdate.setString(10, new Integer(Integer.parseInt(ss[13], 16)).toString());
			pstmtupdate.setString(11, new Integer(Integer.parseInt(ss[14], 16)).toString());
			pstmtupdate.setString(12, new Integer(Integer.parseInt(ss[15], 16)).toString());
			pstmtupdate.setString(13, new Integer(Integer.parseInt(ss[16], 16)).toString());

			pstmtupdate.setInt(14, primaryfilterlife_overdue);
			pstmtupdate.setInt(15, electrostaticdustoflife_overdue);
			pstmtupdate.setInt(16, highefficiencyfilterlife_overdue);

			pstmtupdate.setString(17, ss[19] + ss[18] + ss[17]);

			int updateCon = pstmtupdate.executeUpdate();
			pstmtupdate.close();

			if (updateCon > 0) {
			} else {

				pstmt2 = con.prepareStatement(sql2);
				pstmt2.setString(1, ss[19] + ss[18] + ss[17]);
				pstmt2.setLong(2, new Date().getTime());
				pstmt2.setString(3, new Integer(Integer.parseInt(ss[2], 16)).toString());
				pstmt2.setString(4, new Integer(Integer.parseInt(ss[3], 16)).toString());
				pstmt2.setString(5, new Integer(Integer.parseInt(ss[4], 16)).toString());
				pstmt2.setString(6, new Integer(Integer.parseInt(ss[5], 16)).toString());
				pstmt2.setString(7, new Integer(Integer.parseInt(ss[7], 16) * 256 + Integer.parseInt(ss[6], 16)).toString());
				pstmt2.setString(8, new Integer(Integer.parseInt(ss[9], 16) * 266 + Integer.parseInt(ss[8], 16)).toString());
				pstmt2.setString(9, new Integer(Integer.parseInt(ss[11], 16) * 256 + Integer.parseInt(ss[10], 16)).toString());
				pstmt2.setString(10, new Integer(Integer.parseInt(ss[12], 16)).toString());
				pstmt2.setString(11, new Integer(Integer.parseInt(ss[13], 16)).toString());
				pstmt2.setString(12, new Integer(Integer.parseInt(ss[14], 16)).toString());
				pstmt2.setString(13, new Integer(Integer.parseInt(ss[15], 16)).toString());
				pstmt2.setString(14, new Integer(Integer.parseInt(ss[16], 16)).toString());

				pstmt2.execute();
				pstmt2.close();

			}

			pstmt3 = con.prepareStatement(sql3);
			pstmt3.setString(1, ss[19] + ss[18] + ss[17]);
			pstmt3.setLong(2, new Date().getTime());
			pstmt3.setString(3, new Integer(Integer.parseInt(ss[2], 16)).toString());
			pstmt3.setString(4, new Integer(Integer.parseInt(ss[3], 16)).toString());
			pstmt3.setString(5, new Integer(Integer.parseInt(ss[4], 16)).toString());
			pstmt3.setString(6, new Integer(Integer.parseInt(ss[5], 16)).toString());
			pstmt3.setString(7, new Integer(Integer.parseInt(ss[7], 16) * 256 + Integer.parseInt(ss[6], 16)).toString());
			pstmt3.setString(8, new Integer(Integer.parseInt(ss[9], 16) * 266 + Integer.parseInt(ss[8], 16)).toString());
			pstmt3.setString(9, new Integer(Integer.parseInt(ss[11], 16) * 256 + Integer.parseInt(ss[10], 16)).toString());
			pstmt3.setString(10, new Integer(Integer.parseInt(ss[12], 16)).toString());
			pstmt3.setString(11, new Integer(Integer.parseInt(ss[13], 16)).toString());
			pstmt3.setString(12, new Integer(Integer.parseInt(ss[14], 16)).toString());
			pstmt3.setString(13, new Integer(Integer.parseInt(ss[15], 16)).toString());
			pstmt3.setString(14, new Integer(Integer.parseInt(ss[16], 16)).toString());

			pstmt3.execute();
			pstmt3.close();

			con.commit();

			flag = true;

		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			ConnectionManager.closeConnection(con, null);
		}

		return flag;
	}

	public synchronized static boolean saveDeviceConfig(String s, Record modelappmsg) {
		boolean flag = false;
		Connection con = null;
		PreparedStatement pstmt = null, pstmtupdate = null,pstmtupdateappmsginfo = null,pstmtinsertappmsginfo = null; //,pstmtupdateaftersetcount=null;
		String[] ss = s.split(" ");
		String sql = "INSERT INTO thing_device_info (reportinginterval,frequency,model,functionalswitch,time_min," + "time_hour,time_day,time_month,time_year,on_min,on_hour,"
				+ "off_min,off_hour,setonoroff,openthedustpm25out,id) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";

		String sqlupdate = "update thing_device_info set reportinginterval=?,frequency=?,model=?,functionalswitch=?,time_min=?," + "time_hour=?,time_day=?,time_month=?,"
				+ "time_year=?,on_min=?,on_hour=?,off_min=?,off_hour=?,setonoroff=?,openthedustpm25out=?,aftersetcount=0  where id=?";
//		String sqlupdateaftersetcount = "update thing_device_info set where id=?";
		
		//Record modelappmsg = Db.findFirst("select *,(select aftersetcount from thing_device_info where id = thing_appmsginfo.id limit 1) serviceaftersetcount from thing_appmsginfo where id=? and appmsg=?",ss[19] + ss[18] + ss[17],s) ;
		
		if(modelappmsg == null){
			try {
				con = ConnectionManager.getConnection(false); // DbKit.getConfig().getConnection();
				
				pstmtupdate = con.prepareStatement(sqlupdate);
	
				pstmtupdate.setString(1, new Integer(Integer.parseInt(ss[2], 16)).toString());
				pstmtupdate.setString(2, new Integer(Integer.parseInt(ss[3], 16)).toString());
				pstmtupdate.setString(3, new Integer(Integer.parseInt(ss[4], 16)).toString());
				pstmtupdate.setString(4, new Integer(Integer.parseInt(ss[5], 16)).toString());
				pstmtupdate.setString(5, new Integer(Integer.parseInt(ss[6], 16)).toString());
				pstmtupdate.setString(6, new Integer(Integer.parseInt(ss[7], 16)).toString());
				pstmtupdate.setString(7, new Integer(Integer.parseInt(ss[8], 16)).toString());
				pstmtupdate.setString(8, new Integer(Integer.parseInt(ss[9], 16)).toString());
				pstmtupdate.setString(9, new Integer(Integer.parseInt(ss[10], 16)).toString());
				pstmtupdate.setString(10, new Integer(Integer.parseInt(ss[11], 16)).toString());
				pstmtupdate.setString(11, new Integer(Integer.parseInt(ss[12], 16)).toString());
				pstmtupdate.setString(12, new Integer(Integer.parseInt(ss[13], 16)).toString());
				pstmtupdate.setString(13, new Integer(Integer.parseInt(ss[14], 16)).toString());
				pstmtupdate.setString(14, new Integer(Integer.parseInt(ss[15], 16)).toString());
				pstmtupdate.setString(15, new Integer(Integer.parseInt(ss[16], 16)).toString());
				pstmtupdate.setString(16, ss[19] + ss[18] + ss[17]);
	
				int updateCon = pstmtupdate.executeUpdate();
				pstmtupdate.close();
	
				if (updateCon > 0) {
	//				pstmtupdateaftersetcount = con.prepareStatement(sqlupdateaftersetcount);
	//				pstmtupdateaftersetcount.setString(1, ss[19] + ss[18] + ss[17]);
	//				pstmtupdateaftersetcount.executeUpdate();
	//				pstmtupdateaftersetcount.close();
				} else {
	
					pstmt = con.prepareStatement(sql);
					// System.out.println(new Integer(Integer.parseInt(ss[2],
					// 16)).toString());
					// System.out.println(new Integer(Integer.parseInt(ss[7],
					// 16)).toString() + new Integer(Integer.parseInt(ss[6],
					// 16)).toString());
					// System.out.println(new Integer(Integer.parseInt(ss[9],
					// 16)).toString() + new Integer(Integer.parseInt(ss[8],
					// 16)).toString());
					// System.out.println(new Integer(Integer.parseInt(ss[11],
					// 16)).toString() + new Integer(Integer.parseInt(ss[10],
					// 16)).toString());
					// System.out.println(new Integer(Integer.parseInt(ss[12],
					// 16)).toString());
	
					pstmt.setString(1, new Integer(Integer.parseInt(ss[2], 16)).toString());
					pstmt.setString(2, new Integer(Integer.parseInt(ss[3], 16)).toString());
					pstmt.setString(3, new Integer(Integer.parseInt(ss[4], 16)).toString());
					pstmt.setString(4, new Integer(Integer.parseInt(ss[5], 16)).toString());
					pstmt.setString(5, new Integer(Integer.parseInt(ss[6], 16)).toString());
					pstmt.setString(6, new Integer(Integer.parseInt(ss[7], 16)).toString());
					pstmt.setString(7, new Integer(Integer.parseInt(ss[8], 16)).toString());
					pstmt.setString(8, new Integer(Integer.parseInt(ss[9], 16)).toString());
					pstmt.setString(9, new Integer(Integer.parseInt(ss[10], 16)).toString());
					pstmt.setString(10, new Integer(Integer.parseInt(ss[11], 16)).toString());
					pstmt.setString(11, new Integer(Integer.parseInt(ss[12], 16)).toString());
					pstmt.setString(12, new Integer(Integer.parseInt(ss[13], 16)).toString());
					pstmt.setString(13, new Integer(Integer.parseInt(ss[14], 16)).toString());
					pstmt.setString(14, new Integer(Integer.parseInt(ss[15], 16)).toString());
					pstmt.setString(15, new Integer(Integer.parseInt(ss[16], 16)).toString());
					pstmt.setString(16, ss[19] + ss[18] + ss[17]);
	
					pstmt.execute();
					pstmt.close();
	
				}
	
				con.commit();
	
				flag = true;
	
			} catch (SQLException e) {
				e.printStackTrace();
			} finally {
				ConnectionManager.closeConnection(con, null);
			}
		}

		return flag;
	}

	public synchronized static boolean updateSetOff(String id) {
		boolean flag = false;
		Connection con = null;
		PreparedStatement pstmt = null;

		String sqlupdate = "update thing_device set seton = 0 where id = ? ";


		try {
			con = ConnectionManager.getConnection(false); // DbKit.getConfig().getConnection();
			pstmt = con.prepareStatement(sqlupdate);
			pstmt.setString(1, id);

			pstmt.executeUpdate();

			con.commit();

			flag = true;
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			ConnectionManager.closeConnection(con, pstmt);
		}
		return flag;
	}

	public synchronized static boolean updateQueryOff(String id) {
		boolean flag = false;
		Connection con = null;
		PreparedStatement pstmt = null;

		String sqlupdate = "update thing_device set queryon = 0 where id = ? ";


		try {
			con = ConnectionManager.getConnection(false); // DbKit.getConfig().getConnection();
			pstmt = con.prepareStatement(sqlupdate);
			pstmt.setString(1, id);

			pstmt.executeUpdate();

			con.commit();

			flag = true;
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			ConnectionManager.closeConnection(con , pstmt);
		}
		return flag;
	}

	public synchronized static boolean saveDeviceFilterData(String s) {
		boolean flag = false;
		Connection con = null;
		PreparedStatement pstmtupdate = null;
		String[] ss = s.split(" ");

		String sqlupdate = "update thing_device set primaryfilterlife=?,electrostaticdustoflife=?,highefficiencyfilterlife=?,primaryfilterlife_overdue=?,"
				+ "electrostaticdustoflife_overdue=?,highefficiencyfilterlife_overdue=? where id=? ";

		try {
			con = ConnectionManager.getConnection(false); // DbKit.getConfig().getConnection();

			pstmtupdate = con.prepareStatement(sqlupdate);
			pstmtupdate.setString(7, ss[19] + ss[18] + ss[17]);
			pstmtupdate.setInt(1, new Integer(Integer.parseInt(ss[3], 16) * 256 + Integer.parseInt(ss[2], 16)));
			pstmtupdate.setInt(2, new Integer(Integer.parseInt(ss[5], 16) * 256 + Integer.parseInt(ss[4], 16)));
			pstmtupdate.setInt(3, new Integer(Integer.parseInt(ss[7], 16) * 256 + Integer.parseInt(ss[6], 16)));
			pstmtupdate.setInt(4, new Integer(Integer.parseInt(ss[8], 16)));
			pstmtupdate.setInt(5, new Integer(Integer.parseInt(ss[9], 16)));
			pstmtupdate.setInt(6, new Integer(Integer.parseInt(ss[10], 16)));

			pstmtupdate.executeUpdate();
			pstmtupdate.close();

			con.commit();

			flag = true;

		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			ConnectionManager.closeConnection(con, null);
		}

		return flag;
	}

	public synchronized static boolean saveDeviceFilterSetter(String s) {
		boolean flag = false;
		Connection con = null;
		PreparedStatement pstmtupdate = null;
		String[] ss = s.split(" ");

		String sqlupdate = "update thing_device set primaryfilterlife_set=?,electrostaticdustoflife_set=?,highefficiencyfilterlife_set=?,primaryfilterlife_reset=?,"
				+ "electrostaticdustoflife_reset=?,highefficiencyfilterlife_reset=?,seton=?,queryon=1 where id=? ";

		try {
			con = ConnectionManager.getConnection(false); // DbKit.getConfig().getConnection();

			pstmtupdate = con.prepareStatement(sqlupdate);
			pstmtupdate.setString(8, ss[19] + ss[18] + ss[17]);
			pstmtupdate.setInt(1, new Integer(Integer.parseInt(ss[3], 16) * 256 + Integer.parseInt(ss[2], 16)));
			pstmtupdate.setInt(2, new Integer(Integer.parseInt(ss[5], 16) * 256 + Integer.parseInt(ss[4], 16)));
			pstmtupdate.setInt(3, new Integer(Integer.parseInt(ss[7], 16) * 256 + Integer.parseInt(ss[6], 16)));
			pstmtupdate.setInt(4, new Integer(Integer.parseInt(ss[8], 16)));
			pstmtupdate.setInt(5, new Integer(Integer.parseInt(ss[9], 16)));
			pstmtupdate.setInt(6, new Integer(Integer.parseInt(ss[10], 16)));
			int seton = 0;
			
			if( new Integer(Integer.parseInt(ss[8], 16)) == 1 || new Integer(Integer.parseInt(ss[9], 16)) == 1 || new Integer(Integer.parseInt(ss[10], 16)) == 1){
				seton = 1;
			}
			pstmtupdate.setInt(7, seton);

			pstmtupdate.executeUpdate();
			pstmtupdate.close();

			con.commit();

			flag = true;

		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			ConnectionManager.closeConnection(con, null);
		}

		return flag;
	}
}
